package context.dao;

import context.model.FoodClass;
import context.model.FoodSchool;
import context.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class FoodclassRepository {
    private final String SELECT_BY_ID = "select * from foodclass where foodClassid=?";
    private final String INSERT_USER = "insert into foodclass(foodschoolid,foodClass_word,foodClass_name,foodClass_video,foodClass_date,isfree) values(?,?,?,?,?,?)";

    private JdbcTemplate jdbcTemplate;

    @Autowired          //自动装配
    public FoodclassRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private FoodClass mapRow(ResultSet resultSet, int i) throws SQLException {
        FoodClass result = new FoodClass();
        result.setFoodClassid(resultSet.getLong("foodClassid"));
        result.setFoodschoolid(resultSet.getLong("foodschoolid"));
        result.setFoodClass_name(resultSet.getString("foodClass_name"));
        result.setFoodClass_word(resultSet.getString("foodClass_word"));
        result.setFoodClass_video(resultSet.getString("foodClass_video"));
        result.setFoodClass_date(resultSet.getString("foodClass_date"));
        result.setIsfree(resultSet.getBoolean("isfree"));
        return result;
    }

    //增
    public int insertFoodInformation(final long foodschoolid, final String foodClass_word, final String foodClass_name, final String foodClass_video, final Timestamp foodClass_date, boolean type) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps = jdbcTemplate.getDataSource()
                                .getConnection().prepareStatement(INSERT_USER, new String[]{"foodschoolid", "foodClass_word", "foodClass_name", "foodClass_video", "foodClass_date", "isfree"});
                        ps.setLong(1, foodschoolid);
                        ps.setString(2, foodClass_word);
                        ps.setString(3, foodClass_name);
                        ps.setString(4, foodClass_video);
                        ps.setTimestamp(5, foodClass_date);
                        ps.setBoolean(6, type);
                        return ps;
                    }
                }, keyHolder);
        System.out.println("自动插入id============================" + keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
    }

    //删
    private static final String sql = "delete  from foodclass set foodClassid = ?";

    public void deleteClass(String foodClassid) {
        Object[] obj = new Object[]{foodClassid};
        int i = jdbcTemplate.update(sql, obj);

        if (i > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    //查
    public FoodClass findClassByClassid(long classid) {
        FoodClass result = null;
        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection(); //获得数据库连接
            PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BY_ID);  //创建预备语句
            preparedStatement.setLong(1, classid);        //设置sql语句中的参数值
            ResultSet resultSet = preparedStatement.executeQuery();
            result = new FoodClass();
            result.setFoodClassid(resultSet.getLong("foodClassid"));
            result.setFoodschoolid(resultSet.getLong("foodschoolid"));
            result.setFoodClass_word(resultSet.getString("foodClass_word"));
            result.setFoodClass_video(resultSet.getString("foodClass_video"));
            result.setFoodClass_name(resultSet.getString("foodClass_name"));
            result.setFoodClass_date(resultSet.getString("foodClass_date"));
            result.setIsfree(resultSet.getBoolean("isfree"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    //改
    private static final String sql1 = "update  foodclass set foodschoolid=?,foodClass_word=?,foodClass_video=?,foodClass_date=?,isfree=? where foodClassid = ? ";

    public void updateClass(long foodschoolid, String foodClass_word, String foodClass_video, Timestamp foodClass_date, boolean isfree, long foodClassid) {
        Object args[] = new Object[]{foodschoolid, foodClass_word, foodClass_video, foodClass_date, isfree, foodClassid};
        int temp = jdbcTemplate.update(sql1, args);
        if (temp > 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }

    }
    private final String SELECT_CLASS_BY_ID = "select * from foodclass where foodClassid=?";

    public FoodClass queryFoodClassByID(long foodClassID) {
        try {
            return jdbcTemplate.queryForObject(SELECT_CLASS_BY_ID, this::mapRow, foodClassID);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

}
